﻿using System;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;

namespace MasterMailer
{
   public class DatabaseHelper : IDisposable
   {
      private readonly string _strConnectionString;
      private readonly DbConnection _objConnection;
      private readonly DbCommand _objCommand;
      private readonly DbProviderFactory _objFactory;

      public DatabaseHelper(string connectionstring, Providers provider)
      {
         _strConnectionString = connectionstring;
         switch (provider)
         {
            case Providers.SqlServer:
               _objFactory = SqlClientFactory.Instance;
               break;
            case Providers.OleDb:
               _objFactory = OleDbFactory.Instance;
               break;
            case Providers.ODBC:
               _objFactory = OdbcFactory.Instance;
               break;
            case Providers.ConfigDefined:
               string providername = ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName;
               switch (providername)
               {
                  case "System.Data.SqlClient":
                     _objFactory = SqlClientFactory.Instance;
                     break;
                  case "System.Data.OleDb":
                     _objFactory = OleDbFactory.Instance;
                     break;
                  case "System.Data.Odbc":
                     _objFactory = OdbcFactory.Instance;
                     break;
               }
               break;

         }
         _objConnection = _objFactory.CreateConnection();
         _objCommand = _objFactory.CreateCommand();

         if (_objConnection == null) return;
         _objConnection.ConnectionString = _strConnectionString;
         if (_objCommand != null) _objCommand.Connection = _objConnection;
      }

      public DatabaseHelper(Providers provider)
         : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, provider)
      {
      }

      public DatabaseHelper(string connectionstring)
         : this(connectionstring, Providers.SqlServer)
      {
      }

      public DatabaseHelper()
         : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, Providers.ConfigDefined)
      {
      }

      public bool HandleErrors { get; set; }

      public string LastError { get; private set; }

      public bool LogErrors { get; set; }

      public string LogFile { get; set; }

      public int AddParameter(string name, object value)
      {
         DbParameter p = _objFactory.CreateParameter();
         p.ParameterName = name;
         p.Value = value;
         return _objCommand.Parameters.Add(p);
      }

      public int AddParameter(DbParameter parameter)
      {
         return _objCommand.Parameters.Add(parameter);
      }

      public DbCommand Command
      {
         get
         {
            return _objCommand;
         }
      }

      public void BeginTransaction()
      {
         if (_objConnection.State == System.Data.ConnectionState.Closed)
         {
            _objConnection.Open();
         }
         _objCommand.Transaction = _objConnection.BeginTransaction();
      }

      public void CommitTransaction()
      {
         _objCommand.Transaction.Commit();
         _objConnection.Close();
      }

      public void RollbackTransaction()
      {
         _objCommand.Transaction.Rollback();
         _objConnection.Close();
      }

      public int ExecuteNonQuery(string query)
      {
         return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
      }

      public int ExecuteNonQuery(string query, CommandType commandtype)
      {
         return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
      }

      public int ExecuteNonQuery(string query, ConnectionState connectionstate)
      {
         return ExecuteNonQuery(query, CommandType.Text, connectionstate);
      }

      public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate)
      {
         _objCommand.CommandText = query;
         _objCommand.CommandType = commandtype;
         int i = -1;
         try
         {
            if (_objConnection.State == System.Data.ConnectionState.Closed)
            {
               _objConnection.Open();
            }
            i = _objCommand.ExecuteNonQuery();
         }
         catch (Exception ex)
         {
            HandleExceptions(ex);
         }
         finally
         {
            _objCommand.Parameters.Clear();
            if (connectionstate == ConnectionState.CloseOnExit)
            {
               _objConnection.Close();
            }
         }

         return i;
      }

      public object ExecuteScalar(string query)
      {
         return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
      }

      public object ExecuteScalar(string query, CommandType commandtype)
      {
         return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
      }

      public object ExecuteScalar(string query, ConnectionState connectionstate)
      {
         return ExecuteScalar(query, CommandType.Text, connectionstate);
      }

      public object ExecuteScalar(string query, CommandType commandtype, ConnectionState connectionstate)
      {
         _objCommand.CommandText = query;
         _objCommand.CommandType = commandtype;
         object o = null;
         try
         {
            if (_objConnection.State == System.Data.ConnectionState.Closed)
            {
               _objConnection.Open();
            }
            o = _objCommand.ExecuteScalar();
         }
         catch (Exception ex)
         {
            HandleExceptions(ex);
         }
         finally
         {
            _objCommand.Parameters.Clear();
            if (connectionstate == ConnectionState.CloseOnExit)
            {
               _objConnection.Close();
            }
         }

         return o;
      }

      public DbDataReader ExecuteReader(string query)
      {
         return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
      }

      public DbDataReader ExecuteReader(string query, CommandType commandtype)
      {
         return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
      }

      public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
      {
         return ExecuteReader(query, CommandType.Text, connectionstate);
      }

      public DbDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate)
      {
         _objCommand.CommandText = query;
         _objCommand.CommandType = commandtype;
         DbDataReader reader = null;
         try
         {
            if (_objConnection.State == System.Data.ConnectionState.Closed)
            {
               _objConnection.Open();
            }
            reader = connectionstate == ConnectionState.CloseOnExit ? _objCommand.ExecuteReader(CommandBehavior.CloseConnection) : _objCommand.ExecuteReader();
         }
         catch (Exception ex)
         {
            HandleExceptions(ex);
         }
         finally
         {
            _objCommand.Parameters.Clear();
         }

         return reader;
      }

      public DataSet ExecuteDataSet(string query)
      {
         return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
      }

      public DataSet ExecuteDataSet(string query, CommandType commandtype)
      {
         return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
      }

      public DataSet ExecuteDataSet(string query, ConnectionState connectionstate)
      {
         return ExecuteDataSet(query, CommandType.Text, connectionstate);
      }

      public DataSet ExecuteDataSet(string query, CommandType commandtype, ConnectionState connectionstate)
      {
         DbDataAdapter adapter = _objFactory.CreateDataAdapter();
         _objCommand.CommandText = query;
         _objCommand.CommandType = commandtype;
         if (adapter != null) adapter.SelectCommand = _objCommand;
         DataSet ds = new DataSet();
         try
         {
            if (adapter != null) adapter.Fill(ds);
         }
         catch (Exception ex)
         {
            HandleExceptions(ex);
         }
         finally
         {
            _objCommand.Parameters.Clear();
            if (connectionstate == ConnectionState.CloseOnExit)
            {
               if (_objConnection.State == System.Data.ConnectionState.Open)
               {
                  _objConnection.Close();
               }
            }
         }
         return ds;
      }

      private void HandleExceptions(Exception ex)
      {
         if (LogErrors)
         {
            WriteToLog(ex.Message);
         }
         if (HandleErrors)
         {
            LastError = ex.Message;
         }
         else
         {
            throw ex;
         }
      }

      private void WriteToLog(string msg)
      {
         StreamWriter writer = File.AppendText(LogFile);
         writer.WriteLine(DateTime.Now + " - " + msg);
         writer.Close();
      }

      public void Dispose()
      {
         _objConnection.Close();
         _objConnection.Dispose();
         _objCommand.Dispose();
      }
   }

   public enum Providers
   {
      SqlServer, OleDb, ODBC, ConfigDefined
   }

   public enum ConnectionState
   {
      KeepOpen, CloseOnExit
   }
}
